﻿Imports Entidades.ELocalidad
Imports System.Data.SqlClient
Public Class Dcliente

    Inherits conexion
    Dim cmd As New SqlCommand
    Public Function insertarcliente(ByVal item As Entidades.Ecliente) As Boolean
        Try
            conectado()

            cmd = New SqlCommand("insertar_persona")
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = cnn

            cmd.Parameters.AddWithValue("@dni", item.dnipers)
            cmd.Parameters.AddWithValue("@nombreyapellido", item.nomyapepers)
            cmd.Parameters.AddWithValue("@edad", item.edadpers)
            cmd.Parameters.AddWithValue("@fecha_nac", item.fechanacpers)
            cmd.Parameters.AddWithValue("@dom", item.dompers)
            cmd.Parameters.AddWithValue("@tel1", item.tel1pers)
            cmd.Parameters.AddWithValue("@tel2", item.tel2pers)
            cmd.Parameters.AddWithValue("@rec_sueldo", item.recsueldocli)
            cmd.Parameters.AddWithValue("@grupo_familiar", item.grupofamcli)
            cmd.Parameters.AddWithValue("@loca", item.codlocpers)
            cmd.Parameters.AddWithValue("@email", item.emailpers)

            If cmd.ExecuteNonQuery Then
                Return True
            Else
                Return False
            End If



        Catch ex As Exception
            MsgBox(ex.Message)
            Return False
        Finally
            desconectado()
        End Try
    End Function

    Public Function mostrarcliente() As DataTable
        Try
            conectado()
            cmd = New SqlCommand("select * from CLIENTE")

            cmd.Connection = cnn

            Dim DA As New SqlDataAdapter(cmd)
            Dim Tabla As New DataTable
            Tabla.Clear()
            DA.Fill(Tabla)
            Return Tabla
            Return Nothing
        Catch ex As Exception
            MsgBox(ex.Message)
            Return Nothing
        Finally
            desconectado()
        End Try
    End Function


    Public Function llenarcombo() As DataTable
        Try
            conectado()
            cmd = New SqlCommand("llenarcombolocalidad")
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = cnn

            Dim DA As New SqlDataAdapter(cmd)
            Dim TABLA As New DataTable
            TABLA.Clear()
            DA.Fill(TABLA)
            Return TABLA
            Return Nothing
        Catch ex As Exception
            MsgBox(ex.Message)
            Return Nothing
        Finally
            desconectado()
        End Try
    End Function

    Public Function buscarcliente() As DataTable
        Try
            conectado()
            cmd = New SqlCommand("mostarpersona")
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = cnn

            Dim DA As New SqlDataAdapter(cmd)
            Dim TABLA As New DataTable
            TABLA.Clear()
            DA.Fill(TABLA)
            Return TABLA

            Return Nothing

        Catch ex As Exception
            MsgBox(ex.Message)
            Return Nothing
        Finally
            desconectado()
        End Try
    End Function
    Public Function ver() As List(Of Entidades.Ecliente)
        conectado()
        Dim listaEcliente As New List(Of Entidades.Ecliente)
        Dim objEcliente As Entidades.Ecliente
        cmd.Connection = cnn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "Select * from CLIENTE "

        Dim reader As SqlDataReader
        reader = cmd.ExecuteReader

        While reader.Read
            objEcliente = New Entidades.Ecliente
            objEcliente.dnipers = reader(0)
            objEcliente.nomyapepers = reader(1)
            objEcliente.dnipers = reader(2)
            objEcliente.dompers = reader(3)
            objEcliente.tel1pers = reader(4)
            objEcliente.tel2pers = reader(5)
            objEcliente.codlocpers = reader(6)
            objEcliente.edadpers = reader(7)
            objEcliente.fechanacpers = reader(8)
            objEcliente.grupofamcli = reader(9)
            objEcliente.emailpers = reader(10)
            objEcliente.recsueldocli = reader(11)
            listaEcliente.Add(objEcliente)
        End While
        Return listaEcliente


        desconectado()


    End Function
    Public Function busquedaxnombrecli(ByVal nombre As String) As DataTable
        Try
            conectado()
            cmd = New SqlCommand("busquedadeclinom")
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = cnn
            cmd.Parameters.AddWithValue("@nombreyapellido", nombre)


            Dim DA As New SqlDataAdapter(cmd)
            Dim TABLA As New DataTable
            TABLA.Clear()
            DA.Fill(TABLA)
            Return TABLA
            Return Nothing
        Catch ex As Exception
            MsgBox(ex.Message)
            Return Nothing
        Finally
            desconectado()
        End Try
    End Function
    Public Function busquedaxdnicli(ByVal dni As String) As DataTable
        Try
            conectado()
            cmd = New SqlCommand("busquedadednicli")
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = cnn
            cmd.Parameters.AddWithValue("@dni", dni)


            Dim DA As New SqlDataAdapter(cmd)
            Dim TABLA As New DataTable
            TABLA.Clear()
            DA.Fill(TABLA)
            Return TABLA
            Return Nothing
        Catch ex As Exception
            MsgBox(ex.Message)
            Return Nothing
        Finally
            desconectado()
        End Try
    End Function



End Class
